/*
 * EnigmaGM - A feature rich guild management system.
 * Copyright (C) 2007 ajiaojr.
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301, USA.
 */
package org.ajiaojr.enigmagm.db.mysql;

import org.ajiaojr.enigmagm.client.data.Point;
import org.ajiaojr.enigmagm.client.service.DataAccessException;
import org.ajiaojr.enigmagm.db.ConnectionManager;
import org.ajiaojr.enigmagm.db.PointDAO;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class MySQLPointDAOImpl implements PointDAO {

  private static final Log LOG = LogFactory.getLog(MySQLPointDAOImpl.class);

  public MySQLPointDAOImpl() {
  }

  public List findByCharacterID(int characterID, int firstResult, int maxResults)
      throws DataAccessException {
    Connection c = null;

    try {
      c = ConnectionManager.getInstance().getConnection();

      List result = new ArrayList();

      PreparedStatement s =
          c
              .prepareStatement("SELECT character_name, class_name, point_system_id, point_system_name, current, earned, spent, adjustment FROM point_system NATURAL JOIN point NATURAL JOIN character_list WHERE character_id = ? ORDER BY point_system_id LIMIT ?, ?");
      s.setInt(1, characterID);
      s.setInt(2, firstResult);
      s.setInt(3, maxResults);

      ResultSet r = s.executeQuery();

      while (r.next()) {
        result.add(new Point(characterID, r.getString(1), r.getString(2), r
            .getInt(3), r.getString(4), r.getDouble(5), r.getDouble(6), r
            .getDouble(7), r.getDouble(8)));
      }

      r.close();
      s.close();

      return result;
    } catch (Exception e) {
      throw new DataAccessException(e.getMessage());
    } finally {
      try {
        c.close();
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
    }
  }

  public List findByPointSystemID(int pointSystemId, String sortAttribute,
      boolean ascending, int firstResult, int maxResults)
      throws DataAccessException {
    Connection c = null;

    try {
      c = ConnectionManager.getInstance().getConnection();

      List result = new ArrayList();

      Statement s = c.createStatement();

      String sql =
          "SELECT character_id, character_name, class_name, point_system_name, current, earned, spent, adjustment FROM point_system NATURAL JOIN point NATURAL JOIN character_list WHERE point_system_id = "
              + pointSystemId
              + " AND active=1 ORDER BY "
              + sortAttribute
              + " "
              + (ascending ? "ASC" : "DESC")
              + " LIMIT "
              + firstResult
              + ", "
              + maxResults;

      if (LOG.isDebugEnabled()) {
        LOG.debug("Listing points by point system id using query: " + sql);
      }

      ResultSet r = s.executeQuery(sql);

      while (r.next()) {
        result.add(new Point(r.getInt(1), r.getString(2), r.getString(3),
            pointSystemId, r.getString(4), r.getDouble(5), r.getDouble(6), r
                .getDouble(7), r.getDouble(8)));
      }

      r.close();
      s.close();

      return result;
    } catch (Exception e) {
      throw new DataAccessException(e.getMessage());
    } finally {
      try {
        c.close();
      } catch (SQLException e) {
        LOG.error("SQL Exception", e);
      }
    }
  }
}
